<?php
/**
 * excel 操作辅助类
 */
class ExcelHelper {

    /**
     * 最多加载行数
     */
    const LOAD_MAX_ROW = 100000;

    /**
     * 最多加载列数
     */
    const LOAD_MAX_COLUMN = 128;

    /**
     * 将 excel 载入，读取第一个sheet并转为二维数组
     * @param string $excelPath
     */
    public static function loadExcel($excelPath, $sheetIndex = 0) {

        $fileExt = strtolower(trim(substr(strrchr($excelPath, '.'), 1)));
        if ($fileExt == 'xls') {
            $reader = PHPExcel_IOFactory::createReader('Excel5');
        } else {
            $reader = PHPExcel_IOFactory::createReader('Excel2007');
        }
        // $reader->setReadDataOnly('true');
        $sheets = $reader->load($excelPath, 'utf8');
        $sheet = $sheets->getSheet($sheetIndex);
        $rowIndex = $sheet->getHighestRow();
        $rowIndex = min(self::LOAD_MAX_ROW, $rowIndex);
        $fieldCount = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
        $fieldCount = min(self::LOAD_MAX_COLUMN, $fieldCount);
        $fields = array();
        for ($i = 0; $i < $fieldCount; $i++) {
            $fields[$i] = trim($sheet->getCellByColumnAndRow($i, 1)->getValue());
        }
//        echo 'row count: ', $rowIndex, PHP_EOL;
//        echo 'field count: ', $fieldCount, PHP_EOL;
//        print_r($fields);
        $dataset = array();
        for ($i = 2; $i <= $rowIndex; $i++) {
            $row = [];
            $isValid = false;
            for ($j = 0; $j < $fieldCount; $j++) {
                $field = $fields[$j];
                if (!$field) {
                    continue;
                }
                $value = trim($sheet->getCellByColumnAndRow($j, $i)->getFormattedValue());
                $row[$field] = $value;
                // 任意列有值，视为有效
                if ($value) {
                    $isValid = true;
                }
            }
            if (!$isValid) {
                continue;
            }
            $dataset[] = $row;
        }

        return $dataset;
    }

    /**
     * 根据 dataset（但 sheet） 生成 Excel 文件
     * @param type $dataset
     * @param type $savePath
     */
    public static function dataset2excel($dataset, $savePath) {

        if (!$dataset || !is_array($dataset)) {
            return false;
        }

        return self::tableSheets2Excel(['Sheet1' => $dataset], $savePath);
    }

    /**
     * 根据 table （多 sheet）生成Excel文件
     * @param type $tableSheets
     * @param type $savePath
     */
    public static function tableSheets2Excel($tableSheets, $savePath) {

        if (!$tableSheets || !is_array($tableSheets)) {
            return false;
        }

        $objPHPExcel = new PHPExcel();

        $sheetIdx = 0;
        foreach ($tableSheets as $sheetName => $dataset) {

            if (!$dataset) {
                continue;
            }

            // 第二页起，需要先创建
            if ($sheetIdx) {
                $objPHPExcel->createSheet($sheetIdx);
            }
            $workSheet = $objPHPExcel->setActiveSheetIndex($sheetIdx);
            $sheetIdx++;

            // title
            $workSheet->setTitle($sheetName);

            // 表头
            $rowIndex = 1;
            $fields = array_keys($dataset[0]);
            $columnIndex = 0;
            foreach ($fields as $label) {
                $workSheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $label, PHPExcel_Cell_DataType::TYPE_STRING);
                $columnIndex++;
            }
            // 数据
            foreach ($dataset as $item) {
                $columnIndex = 0;
                $rowIndex++;
                foreach ($fields as $label) {
                    $value = isset($item[$label]) ? trim($item[$label]) : "";
                    $workSheet->setCellValueExplicit(PHPExcel_Cell::stringFromColumnIndex($columnIndex) . $rowIndex, $value, PHPExcel_Cell_DataType::TYPE_STRING);
                    $columnIndex++;
                }
            }
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        if (!is_dir(dirname($savePath))) {
            mkdir(dirname($savePath), 0777, true);
        }
        $objWriter->save($savePath);
        return true;
    }
}
